import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 数据文件路径
const dataPath = path.join(__dirname, '../../data/database.json');

// 日志记录器
const logger = {
  info: (msg, data) => console.log(`INFO: ${msg}`, data || ''),
  error: (msg, data) => console.error(`ERROR: ${msg}`, data || ''),
  warn: (msg, data) => console.warn(`WARN: ${msg}`, data || '')
};

// 内存数据库
const memoryDB = {
  doctors: [],
  patients: [],
  scoring_tasks: [],
  scoring_results: []
};

// 加载数据
const loadData = () => {
  try {
    if (fs.existsSync(dataPath)) {
      const data = fs.readFileSync(dataPath, 'utf8');
      const parsed = JSON.parse(data);
      Object.assign(memoryDB, parsed);
      logger.info('✅ 数据加载成功', { records: Object.keys(parsed).map(k => `${k}: ${parsed[k].length}`) });
    } else {
      // 初始化默认数据
      memoryDB.doctors = [{
        doctor_id: 1,
        username: "admin",
        real_name: "系统管理员",
        department: "信息科",
        title: "系统管理员",
        password_hash: "Admin123",
        salt: "admin_salt_2024",
        status: 1,
        role: 3
      }];
      saveData();
      logger.info('✅ 初始化默认数据');
    }
  } catch (error) {
    logger.error('加载数据失败:', error);
  }
};

// 保存数据
const saveData = () => {
  try {
    const dataDir = path.dirname(dataPath);
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }
    fs.writeFileSync(dataPath, JSON.stringify(memoryDB, null, 2));
  } catch (error) {
    logger.error('保存数据失败:', error);
  }
};

// 初始加载
loadData();

// 测试连接
export const testConnection = async () => {
  logger.info('✅ 持久化数据库连接成功');
  logger.info(`📍 数据文件: ${dataPath}`);
  return true;
};

// 执行查询
export const query = async (sql, params = []) => {
  logger.info(`执行查询: ${sql}`);
  if (params.length > 0) {
    logger.info(`参数:`, params);
  }

  // 简单的SQL解析器（仅支持基本操作）
  const sqlLower = sql.toLowerCase().trim();
  let result = [];

  try {
    if (sqlLower.startsWith('select')) {
      // 模拟SELECT查询
      if (sqlLower.includes('from doctors')) {
        result = memoryDB.doctors;
      } else if (sqlLower.includes('from patients')) {
        result = memoryDB.patients;
      } else if (sqlLower.includes('from scoring_tasks')) {
        result = memoryDB.scoring_tasks;
      } else if (sqlLower.includes('from scoring_results')) {
        result = memoryDB.scoring_results;
      }

      // 处理WHERE条件
      if (sqlLower.includes('where')) {
        if (sqlLower.includes('where username =')) {
          const username = params[0];
          result = result.filter(r => r.username === username);
        } else if (sqlLower.includes('where task_id =')) {
          const taskId = params[0];
          result = result.filter(r => r.task_id === taskId);
        } else if (sqlLower.includes('where patient_code like')) {
          const code = params[0];
          result = result.filter(r => r.patient_code && r.patient_code.includes(code.replace(/%/g, '')));
        }
      }

      // 处理JOIN - 修复查询逻辑
      if (sqlLower.includes('join')) {
        // 复杂查询的处理
        const fromTasks = sqlLower.includes('from scoring_tasks');
        const joinPatients = sqlLower.includes('left join patients');
        const joinDoctors = sqlLower.includes('left join doctors');
        const joinResults = sqlLower.includes('left join scoring_results');

        if (fromTasks && joinPatients && joinDoctors) {
          result = memoryDB.scoring_tasks.map(task => {
            const patient = memoryDB.patients.find(p => p.patient_id === task.patient_id);
            const doctor = memoryDB.doctors.find(d => d.doctor_id === task.doctor_id);
            const resultItem = joinResults ? memoryDB.scoring_results.find(r => r.task_id === task.task_id) : null;

            return {
              task_id: task.task_id,
              patient_id: task.patient_id,
              doctor_id: task.doctor_id,
              status: task.status,
              submit_time: task.submit_time,
              complete_time: task.complete_time,
              image_url: task.image_url,
              clinical_text: task.clinical_text,
              patient_code: patient?.patient_code || null,
              gender: patient?.gender || 0,
              birthday: patient?.birthday || null,
              real_name: doctor?.real_name || null,
              department: doctor?.department || null,
              title: doctor?.title || null,
              score: resultItem?.score || null,
              severity: resultItem?.severity || null,
              confidence: resultItem?.confidence || null,
              model_version: resultItem?.model_version || null,
              analysis_details: resultItem?.analysis_details || null
            };
          });
        }
      }

      // 处理COUNT
      if (sqlLower.includes('count(*)')) {
        result = [{ count: result.length }];
      }

      // 处理ORDER BY和LIMIT
      if (sqlLower.includes('order by') && sqlLower.includes('desc')) {
        result = result.reverse();
      }
      if (sqlLower.includes('limit')) {
        const limitMatch = sql.match(/limit\s+(\d+)/i);
        if (limitMatch) {
          const limit = parseInt(limitMatch[1]);
          result = result.slice(0, limit);
        }
      }

    } else if (sqlLower.startsWith('insert')) {
      // 模拟INSERT
      const lastInsertId = Date.now();

      if (sqlLower.includes('into doctors')) {
        const doctor = {
          doctor_id: lastInsertId,
          username: params[0],
          password_hash: params[1],
          salt: params[2],
          real_name: params[3],
          department: params[4],
          title: params[5],
          role: params[6]
        };
        memoryDB.doctors.push(doctor);
        result = [{ insertId: doctor.doctor_id }];

      } else if (sqlLower.includes('into patients')) {
        const patient = {
          patient_id: lastInsertId,
          patient_code: params[0]
        };
        memoryDB.patients.push(patient);
        result = [{ insertId: patient.patient_id }];

      } else if (sqlLower.includes('into scoring_tasks')) {
        const task = {
          task_id: lastInsertId,
          patient_id: params[0],
          doctor_id: params[1],
          status: params[2] || 1,
          submit_time: new Date(),
          image_url: params[3],
          clinical_text: params[4]
        };
        memoryDB.scoring_tasks.push(task);
        result = [{ insertId: task.task_id }];

      } else if (sqlLower.includes('into scoring_results')) {
        const result_item = {
          result_id: lastInsertId,
          task_id: params[0],
          score: params[1],
          severity: params[2],
          confidence: params[3],
          model_version: params[4],
          analysis_details: params[5]
        };
        memoryDB.scoring_results.push(result_item);
        result = [result_item];
      }

      saveData(); // 保存数据

    } else if (sqlLower.startsWith('update')) {
      // 模拟UPDATE
      if (sqlLower.includes('update scoring_tasks set status =')) {
        const taskId = params[0];
        const task = memoryDB.scoring_tasks.find(t => t.task_id === taskId);
        if (task) {
          task.status = params[1];
          task.complete_time = new Date();
          saveData();
        }
      }
      result = [];

    } else if (sqlLower.startsWith('create table')) {
      // 表创建模拟
      logger.info('表创建模拟:', sql);
      result = [];
    }

    logger.info(`查询完成: ${result.length} 条记录`);
    return result;

  } catch (error) {
    logger.error('查询错误:', error);
    throw error;
  }
};

// 执行函数
export const execute = query;

// 事务函数
export const transaction = async (callback) => {
  const conn = {
    query: query,
    execute: async (sql, params) => {
      logger.info('事务执行SQL:', { sql, params });
      const result = await query(sql, params);
      logger.info('事务执行结果:', { result });

      // 对于INSERT操作，返回特殊格式
      if (sql.includes('INSERT') && result && result.insertId) {
        logger.info('INSERT操作返回insertId:', result.insertId);
        return [[{ insertId: result.insertId }], []];
      }

      // MySQL2的execute方法返回 [rows, fields] 格式
      // 确保result始终是数组，避免undefined错误
      const rows = Array.isArray(result) ? result : (result ? [result] : []);
      return [rows, []];
    }
  };
  return await callback(conn);
}


export default {
  query,
  testConnection,
  transaction,
  execute
};